package model;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import beans.MessageBean;

public class CityRegionDAO {
	static Connection currentCon = null;
	static ResultSet rs = null;

	public static ArrayList<String> getRegions(String country) {
		Statement stmt = null;    
		String searchQuery = "select name from region where country='" + country +"' order by name asc";

		System.out.println("Query: " + searchQuery);

		ArrayList<String> regionList = new ArrayList<String>();

		try {
			//connect to DB 
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);	
			while(rs.next()) {
				regionList.add(rs.getString("name"));
			}
		} 

		catch (Exception ex) {
			System.out.println("FAIL!: An Exception has occurred! " + ex);
		} 

		//some exception handling
		finally {
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {}
				currentCon = null;
			}
		}
		return regionList;
	}	

	public static ArrayList<String> getCities(String country, String region) {
		Statement stmt = null;    
		String searchQuery = "select name from city where country='" + country +"' and region='" + region + "' order by name asc";

		System.out.println("Query: " + searchQuery);

		ArrayList<String> regionList = new ArrayList<String>();

		try {
			//connect to DB 
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);	
			while(rs.next()) {
				regionList.add(rs.getString("name"));
			}
		} 

		catch (Exception ex) {
			System.out.println("FAIL!: An Exception has occurred! " + ex);
		} 

		//some exception handling
		finally {
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {}
				currentCon = null;
			}
		}
		return regionList;
	}
}

